# python 3.6
# -*- coding:utf-8 -*-
from time import sleep

import requests
import json

import pymysql



def queryApp(url):
    querystring = {"xxx_api_auth": "6165633461336661636365646366616233613166346630633062653033326334"}

    headers = {
        'cache-control': "no-cache"
    }

    response = requests.request("GET", url, headers=headers, params=querystring)
    dat = json.loads(response.text)
    js = get_pretty_print(dat)
    dealData(js)
    return js


# 格式化json输出
def get_pretty_print(json_object):
    json_return = json.dumps(json_object, sort_keys=True, indent=4, separators=(',', ': '), ensure_ascii=False)
    tmp = json.loads(json_return)
    return tmp['data']['vodrows']


def buildUrl():
    #  运行到286页；   运行到759页  二次爬取 759
    for i in range(759, 759):
        # https://yv7uq72g81a2g8e69mgw.xiaoxiaoimg.com/vod/listing-0-0-0-0-0-0-1?apiVersion=28&deviceModel=MI%206X&brand=xiaomi&deviceName=wayne&serial=1f96942d&platform=android&version=2.1.32
        url = "https://yv7uq72g81a2g8e69mgw.xiaoxiaoimg.com/vod/listing-0-0-0-0-0-0-" + str(i) + \
              "?apiVersion=28&deviceModel=MI%206X&brand=xiaomi&deviceName=wayne&serial=1f96942d&platform=android&version=2.1.32"
        print(url)
        try:
            queryApp(url)
        except :
            print("出现错误！")
        print("============= 睡5秒钟 =================")
        sleep(5)


def dealData(tmp):
    conn = pymysql.connect(
        host='120.27.233.178',  # mysql服务器地址
        port=3306,  # 端口号
        user='root',  # 用户名
        passwd='hxf',  # 密码
        db='yaoqishan',  # 数据库名称
        charset='utf8',  # 连接编码，根据需要填写
    )
    cur = conn.cursor()  # 创建并返回游标

    x = len(tmp)
    k = 0
    sql_middle = ") values ("
    sql_tail = ");"

    while k < x:
        #  数据填充的语句
        sql = "insert into xx_videos_1 ( "
        sql_names = ""
        sql_values = ""
        items = tmp[k].items()
        for key, value in items:
            if str(key).__eq__("intro"):
                sql_names += str(key) + " ,"
                sql_values += "'" + str(value).replace(" ", "").replace("\r", "").replace("\n", "") + "',"

                continue

            if str(key).__eq__("langvoicename") | str(key).__eq__("yearname") | str(key).__eq__("areaname") | str(
                    key).__eq__("catename"):
                pass
            else:
                if str(key).__eq__("tags"):
                    # tags 特殊处理 将里面的tagid 放出来；
                    # tags = json.loads(str(value))
                    # print("我是tags里面的东西：   " + str(value))
                    if len(value) > 0:
                        sql_names += " tagid,"
                        sql_values += "\"" + value[0]['tagid'] + "\","
                    else:
                        sql_names += " tagid,"
                        sql_values += "'',"
                else:
                    if str(key).__eq__("playlist") | str(key).__eq__("downlist"):
                        if str(key).__eq__("playlist"):
                            sql_names += "playlistnum" + " ,"
                            sql_values += "" + str(len(value)) +  ","
                            print("集数:   " +  str(len(value)))
                        else:
                            # print("" + str(key))
                            sql_names += str(key) + " ,"
                            sql_values += "\"" + "\","
                    else:
                        if str(key).__eq__("actor_tags") | str(key).__eq__("director_tags"):
                            sql_names += str(key) + " ,"
                            sql_values += "\"" + str(value) + "\","
                        else:
                            sql_names += str(key) + " ,"
                            sql_values += "'" + str(value) + "',"

        sql += str(sql_names)[:-1] + sql_middle + str(sql_values)[:-1] + sql_tail
        # print(sql)
        print("-----------------插入一条数据 --------------")

        k = k + 1
        cur.execute(sql)  # 执行上述sql命令
        conn.commit()


buildUrl()

# queryApp()
